package edu.udlap.spring2008.is300.finalexam.app.mvc.model.database;

import edu.udlap.spring2008.is300.finalexam.app.mvc.model.database.objects.AppCategoryRecord;
import edu.udlap.spring2008.is300.finalexam.app.mvc.model.database.objects.AppFormatRecord;
import edu.udlap.spring2008.is300.finalexam.app.mvc.model.database.objects.AppImageRecord;
import edu.udlap.spring2008.is300.finalexam.app.mvc.model.database.objects.AppPrivacyLevelRecord;
import edu.udlap.spring2008.is300.finalexam.app.mvc.model.database.objects.AppSizeRecord;
import edu.udlap.spring2008.is300.finalexam.framework.mvc.model.database.objects.CategoryRecord;
import edu.udlap.spring2008.is300.finalexam.framework.mvc.model.database.objects.FormatRecord;
import edu.udlap.spring2008.is300.finalexam.framework.mvc.model.database.objects.ImageRecord;
import edu.udlap.spring2008.is300.finalexam.framework.mvc.model.database.objects.PrivacyLevelRecord;
import edu.udlap.spring2008.is300.finalexam.framework.mvc.model.database.objects.SizeRecord;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Ruslán Ledesma Garza
 */
public class DatabaseFacade
{
    private Driver driver_; ///database driver to use...
    private Connection connection_; ///database connection to use...
            
    private String connectionString_;
    private String usernameString_;
    private String passwordString_;
    
    public DatabaseFacade(Driver driver,
                          String connectionString,
                          String usernameString,
                          String passwordString)
    {
        driver_ = driver;
        connectionString_ = connectionString;
        usernameString_ = usernameString;
        passwordString_ = passwordString;
    }
    
    public boolean registerDriver()
    {
        try
        {
            DriverManager.registerDriver(driver_);
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.registerDriver()");
            return false;
        }
        
        return true;
    }
    
    public boolean unregisterDriver()
    {
        try
        {
            DriverManager.deregisterDriver(driver_);
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.unregisterDriver()");
            return false;
        }
        
        return true;
    }
    
    public boolean connect()
    {   
        try
        {
            connection_ = DriverManager.getConnection(connectionString_, usernameString_, passwordString_);
            connection_.setAutoCommit(false);
            connection_.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.connect()");
            return false;
        }
        
        return true;
    }
 
    public boolean closeConnection()
    {
        try
        {
            connection_.close();
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.closeConnection()");
            return false;
        }
        
        return true;        
    }

    public Driver getDriver()
    {
        return driver_;
    }

    public Connection getConnection()
    {
        return connection_;
    }

    public String getConnectionString()
    {
        return connectionString_;
    }
    
    public String getUsernameString()
    {
        return usernameString_;
    }

    public String getPasswordString()
    {
        return passwordString_;
    } 
    
    public ArrayList<ImageRecord> getMatchingImages(String queryContraints)
    {
        ArrayList<ImageRecord> result = new ArrayList<ImageRecord>();
        
        try
        {
            
            Statement stmt = connection_.createStatement();
            ResultSet records = null;
            if(queryContraints.trim().length() == 0)
                records = stmt.executeQuery("SELECT * FROM \"IMG.IMAGE\" " + "ORDER BY \"ID_IMAGE\"");
            else
                records = stmt.executeQuery("SELECT * FROM \"IMG.IMAGE\" WHERE " +
                        queryContraints + " ORDER BY \"ID_IMAGE\"");
            
            while(records.next())
            {
                //TODO: Replace with a more efficient sql query (i.e. no calls to
                ///object getter methods...
                result.add(new AppImageRecord(
                        records.getInt("ID_IMAGE"),
                        records.getString("ADDRESS"),
                        records.getBoolean("IS_FAVORITE"),
                        records.getString("DESCRIPTION"),
                        getCategoryRecord(records.getInt("ID_CATEGORY")),
                        getSizeRecord(records.getInt("ID_SIZE")),
                        getFormatRecord(records.getInt("ID_FORMAT")),
                        getPrivacyLevelRecord(records.getInt("ID_PRIVACY"))
                        ));
            }
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.getMatchingImages()");
            return null;
        }
        
        return result;
    }
    
    public CategoryRecord getCategoryRecord(int id)
    {
        CategoryRecord result = null;
        
        try
        {
            PreparedStatement pstmt = connection_.prepareStatement(
                    "SELECT * FROM \"IMG.CATEGORY\" " +
                    "WHERE \"ID_CATEGORY\"=?");
            
            pstmt.setInt(1, id);
            
            ResultSet records = pstmt.executeQuery();
            
            if(records.next())
                result = new AppCategoryRecord(id, records.getString("CATEGORY"));
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.getCategoryRecord()");
            return null;
        }
        
        return result;
    }

    public SizeRecord getSizeRecord(int id)
    {
        SizeRecord result = null;
        
        try
        {
            PreparedStatement pstmt = connection_.prepareStatement(
                    "SELECT * FROM \"IMG.SIZE\" " +
                    "WHERE \"ID_SIZE\"=?");
            
            pstmt.setInt(1, id);
            
            ResultSet records = pstmt.executeQuery();
            
            if(records.next())
                result = new AppSizeRecord(id, records.getString("SIZE"));
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.getSizeRecord()");
            return null;
        }
        
        return result;
    }    
 
     public FormatRecord getFormatRecord(int id)
    {
        FormatRecord result = null;
        
        try
        {
            PreparedStatement pstmt = connection_.prepareStatement(
                    "SELECT * FROM \"IMG.FORMAT\" " +
                    "WHERE \"ID_FORMAT\" = ?");
            
            pstmt.setInt(1, id);
            
            ResultSet records = pstmt.executeQuery();
            
            if(records.next())
                result = new AppFormatRecord(id, records.getString("FORMAT"));
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.getFormatRecord()");
            return null;
        }
        
        return result;
    }   
    
      public PrivacyLevelRecord getPrivacyLevelRecord(int id)
    {
        PrivacyLevelRecord result = null;
        
        try
        {
            PreparedStatement pstmt = connection_.prepareStatement(
                    "SELECT * FROM \"IMG.PRIVACY\" " +
                    "WHERE \"ID_PRIVACY\" = ?");
            
            pstmt.setInt(1, id);
            
            ResultSet records = pstmt.executeQuery();
            
            if(records.next())
                result = new AppPrivacyLevelRecord(id, records.getString("PRIVACY"));
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.getPrivacyLevelRecord()");
            return null;
        }
        
        return result;
    }   
    public ImageRecord getImageRecord(int id)
    {
        ImageRecord result = null;
        
        try
        {
            PreparedStatement pstmt = connection_.prepareStatement(
                    "SELECT * FROM \"IMG.IMAGE\" " +
                    "WHERE \"ID_IMAGE\"=?");
            
            pstmt.setInt(1, id);
            
            ResultSet records = pstmt.executeQuery();
            
            if(records.next())
                        result = new AppImageRecord(
                            records.getInt("ID_IMAGE"),
                            records.getString("ADDRESS"),
                            records.getBoolean("IS_FAVORITE"),
                            records.getString("DESCRIPTION"),
                            getCategoryRecord(records.getInt("ID_CATEGORY")),
                            getSizeRecord(records.getInt("ID_SIZE")),
                            getFormatRecord(records.getInt("ID_FORMAT")),
                            getPrivacyLevelRecord(records.getInt("ID_PRIVACY"))
                            );
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.getImageRecord()");
            return null;
        }
        
        return result;
    }   

    public ArrayList<CategoryRecord> getCategoryRecords() {
        
        ArrayList<CategoryRecord> result = new ArrayList<CategoryRecord>();
        
        ResultSet records = null;       
        
        try
        {
            Statement stmt = connection_.createStatement();
            records = stmt.executeQuery(
                    "SELECT * FROM \"IMG.CATEGORY\" " + " ORDER BY \"ID_CATEGORY\"");
            
            while(records.next())
            {
                result.add(new AppCategoryRecord(records.getInt("ID_CATEGORY"), records.getString("CATEGORY")));
            }   
            
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.getCategoryRecords()");
            return null;
        }
        
        return result;
    }

    public ArrayList<PrivacyLevelRecord> getPrivacyLevelRecords() {
        ArrayList<PrivacyLevelRecord> result = new ArrayList<PrivacyLevelRecord>();
        
        ResultSet records = null;       
        
        try
        {
            Statement stmt = connection_.createStatement();
            records = stmt.executeQuery(
                    "SELECT * FROM \"IMG.PRIVACY\""+ " ORDER BY \"ID_PRIVACY\"");
            
            while(records.next())
            {
                result.add(new AppPrivacyLevelRecord(records.getInt("ID_PRIVACY"), records.getString("PRIVACY")));
            }   
            
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.getPrivacyRecords()");
            return null;
        }
        
        return result;
    }

    public ArrayList<SizeRecord> getSizeRecords() {
        ArrayList<SizeRecord> result = new ArrayList<SizeRecord>();
        
        ResultSet records = null;       
        
        try
        {
            Statement stmt = connection_.createStatement();
            records = stmt.executeQuery(
                    "SELECT * FROM \"IMG.SIZE\""+ " ORDER BY \"ID_SIZE\"");
            
            while(records.next())
            {
                result.add(new AppSizeRecord(records.getInt("ID_SIZE"), records.getString("SIZE")));
            }   
            
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.getSizeRecords()");
            return null;
        }
        
        return result;
    }
    
     public ArrayList<FormatRecord> getFormatRecords() {
        ArrayList<FormatRecord> result = new ArrayList<FormatRecord>();
        
        ResultSet records = null;       
        
        try
        {
            Statement stmt = connection_.createStatement();
            records = stmt.executeQuery(
                    "SELECT * FROM \"IMG.FORMAT\"" + " ORDER BY \"ID_FORMAT\"");
            
            while(records.next())
            {
                result.add(new AppFormatRecord(records.getInt("ID_FORMAT"), records.getString("FORMAT")));
            }   
            
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.getFormatRecords()");
            return null;
        }
        
        return result;
    }
    
    public boolean hasMatchingImages(String queryContraints)
    {
        boolean hasMatchingImages = false;
        
        try
        {
            Statement stmt = connection_.createStatement();
            ResultSet records = null;
            if(queryContraints.trim().length() == 0)
                records = stmt.executeQuery("SELECT * FROM \"IMG.IMAGE\"" + " ORDER BY \"ID_IMAGE\"");
            else
                records = stmt.executeQuery("SELECT * FROM \"IMG.IMAGE\" WHERE" +
                        queryContraints + " ORDER BY \"ID_IMAGE\"");
            
            if(records.next())
                hasMatchingImages=true;
        }
        catch (SQLException ex)
        {
            System.out.println(ex.toString() + "\n...at DatabaseFacade.getMatchingImages()");
            return hasMatchingImages;
        }
        
        return hasMatchingImages;
    }  
    public boolean addImageRecord(ImageRecord imageRecord)
    {
        boolean isAdded = false;
        try
        {
            PreparedStatement pstm = connection_.prepareStatement(
                "INSERT INTO \"IMG.IMAGE\"" +
                "(\"ADDRESS\",\"DESCRIPTION\",\"IS_FAVORITE\",\"ID_CATEGORY\",\"ID_PRIVACY\",\"ID_SIZE\",\"ID_FORMAT\")" +
                "VALUES(?,?,?,?,?,?,?)");
            
            pstm.setString(1, imageRecord.getPath());//address:string
            pstm.setString(2, imageRecord.getDescription());//description:string
            pstm.setBoolean(3, imageRecord.isFavorite());//id_favorite:boolean
            pstm.setInt(4, imageRecord.getCategory().getId());//id_category:integer
            pstm.setInt(5, imageRecord.getPrivacyLevel().getId());//id_privacy:integer
            pstm.setInt(6, imageRecord.getSize().getId());//id_size:integer
            pstm.setInt(7, imageRecord.getFormat().getId());//id_format:integer
        
            pstm.executeUpdate();
            connection_.commit();
            isAdded = true;
        }
        catch(SQLException ex)
        {
            System.out.println(ex.toString()+ "\n... when inserting Image Record.");
        }
        return isAdded;
    }
    
    public boolean deleteImageRecord(int id)
    {
        boolean isDeleted = false;
        try
        {
            PreparedStatement pstm = connection_.prepareStatement(
                    "DELETE FROM \"IMG.IMAGE\" WHERE \"ID_IMAGE\"=?");
            pstm.clearParameters();
            pstm.setInt(1, id);
            pstm.executeUpdate();
            connection_.commit();
            isDeleted = true;
        }
        catch(SQLException ex){
            System.out.println(ex.toString()+"\n...when deleting Image Record.");
        }
        return isDeleted;
    }
    
    
    public boolean updateImageRecord(ImageRecord imageRecord)
    {
        boolean isUpdated = false;
        try
        {
            PreparedStatement pstm = connection_.prepareStatement(
                    "UPDATE \"IMG.IMAGE\"" +
                    "SET \"ADDRESS\" = ?, " +
                        "\"DESCRIPTION\" = ?, " +
                        "\"IS_FAVORITE\" = ?," +
                        "\"ID_CATEGORY\" = ?," + 
                        "\"ID_PRIVACY\" = ?," +
                        "\"ID_SIZE\" = ?,"+ 
                        "\"ID_FORMAT\" = ? " +
                        "WHERE \"ID_IMAGE\" = ?");  
            
            pstm.setString(1, imageRecord.getPath());
            pstm.setString(2, imageRecord.getDescription());
            pstm.setBoolean(3, imageRecord.isFavorite());
            pstm.setInt(4, imageRecord.getCategory().getId());
            pstm.setInt(5, imageRecord.getPrivacyLevel().getId());
            pstm.setInt(6, imageRecord.getSize().getId());
            pstm.setInt(7, imageRecord.getFormat().getId());
            pstm.setInt(8, imageRecord.getId());
            pstm.executeUpdate();
            connection_.commit();
            isUpdated = true;
        }
        catch(SQLException ex)
        {
            System.out.println(ex.toString()+"\n...when updating Image Record.");
        }
        return isUpdated;
    }
}
